﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLiNhaSachDTO;

namespace QuanLiNhaSachDAO
{
    public class NhaXuatBanDAO
    {
        public static DataTable getByMaSach(int MaSach)
        {
            string sql = "SELECT SACHNHAXUATBAN.id, SACHNHAXUATBAN.MaSach, SACHNHAXUATBAN.MaNhaXuatBan, SACHNHAXUATBAN.id AS Expr1, NHAXUATBAN.Ten FROM SACHNHAXUATBAN INNER JOIN NHAXUATBAN ON SACHNHAXUATBAN.MaNhaXuatBan = NHAXUATBAN.id WHERE (SACHNHAXUATBAN.MaSach = " + MaSach + ")";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public static DataTable SelectAll()
        {
            string sql = "select * from NHAXUATBAN";
            SqlDataAdapter sda = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }

        public static void Insert(NhaXuatBan q)
        {
            string sql = "insert into NHAXUATBAN(Ten,DiaChi,DienThoai,Email,GhiChu)" +
                        "values(@Ten,@DiaChi,@DienThoai,@Email,@GhiChu)";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@Ten", q.Ten);
            scmd.Parameters.AddWithValue("@DiaChi", q.DiaChi);
            scmd.Parameters.AddWithValue("@DienThoai", q.DienThoai);
            scmd.Parameters.AddWithValue("@Email", q.Email);
            scmd.Parameters.AddWithValue("@GhiChu", q.GhiChu);
            scmd.ExecuteNonQuery();
        }

        public static void Update(NhaXuatBan q)
        {
            string sql = "update NHAXUATBAN set Ten=@Ten, DiaChi=@DiaChi, DienThoai=@DienThoai," +
                        "Email=@Email, GhiChu=@GhiChu where id=@id";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@Ten", q.Ten);
            scmd.Parameters.AddWithValue("@DiaChi", q.DiaChi);
            scmd.Parameters.AddWithValue("@DienThoai", q.DienThoai);
            scmd.Parameters.AddWithValue("@Email", q.Email);
            scmd.Parameters.AddWithValue("@GhiChu", q.GhiChu);
            scmd.Parameters.AddWithValue("@id", q.Id);
            scmd.ExecuteNonQuery();
        }

        public static void Delete(NhaXuatBan q)
        {
            string sql = "delete from NHAXUATBAN where id = @id";
            SqlCommand scmd = new SqlCommand(sql, sqlDB.MyConnection);
            scmd.Parameters.AddWithValue("@id", q.Id);
            scmd.ExecuteNonQuery();
        }

        public static DataTable search(string id)
        {
            string sql = "SELECT * FROM NHAXUATBAN WHERE id ='" + id + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static DataTable TimSachTuNhaCungCap(string id)
        {
            string sql = "SELECT * FROM SACH WHERE MaNhaXuatBan = '" + id + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public static string TimNXB(string id)
        {
            string sql = "SELECT NHAXUATBAN.Ten FROM SACH INNER JOIN SACHNHAXUATBAN ON SACH.id = SACHNHAXUATBAN.MaSach ";
            sql += "INNER JOIN NHAXUATBAN ON SACHNHAXUATBAN.MaNhaXuatBan = NHAXUATBAN.id ";
            sql += "WHERE SACH.id = " + id + "";
            SqlDataAdapter da = new SqlDataAdapter(sql, sqlDB.MyConnection);
            DataTable dt = new DataTable();
            da.Fill(dt);
            string tmpStr = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tmpStr += dt.Rows[i][0].ToString() + " & ";
            }
            int dodai = tmpStr.Length;
            tmpStr = tmpStr.Remove(dodai - 3);
            return tmpStr;
        }
    }
}
